﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using RainyPWS.Model;
using RainyPWS.Data;
using RainyPWS.Common;

namespace RainyPWS.Business
{
    public class BRecord
    {
        private static BRecord _instance = null;
        public static BRecord Instance
        {
            get
            {
                if (_instance == null)
                    _instance = new BRecord();
                return _instance;
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int InsertData(MRecord obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("insert into TRecord ([Title],[TypeID],[Status],[Author],[AddedDate],[Summary],[KeyWorks],[Content])");
            sql.AppendLine(" values (@Title, @TypeID, @Status, @Author, @AddedDate, @Summary, @KeyWorks, @Content)");

            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(cmd, "@Title", DbType.String, obj.Title);
            db.AddInParameter(cmd, "@TypeID", DbType.Int32, (obj.TypeID == 0) ? (object)DBNull.Value : obj.TypeID);
            db.AddInParameter(cmd, "@Status", DbType.Int32, obj.Status);
            db.AddInParameter(cmd, "@Author", DbType.String, obj.Author);
            db.AddInParameter(cmd, "@AddedDate", DbType.DateTime, obj.AddedDate);
            db.AddInParameter(cmd, "@Summary", DbType.String, obj.Summary);
            db.AddInParameter(cmd, "@KeyWorks", DbType.String, obj.KeyWorks);
            db.AddInParameter(cmd, "@Content", DbType.String, obj.Content);
            try
            {
                int count = db.ExecuteNonQuery(cmd);
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql.ToString());
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int UpdateData(MRecord obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("update TRecord set ");
            sql.AppendLine(" [Title]=@Title,");
            sql.AppendLine(" [TypeID]=@TypeID,");
            sql.AppendLine(" [Status]=@Status,");
            sql.AppendLine(" [Author]=@Author,");
            sql.AppendLine(" [AddedDate]=@AddedDate,");
            sql.AppendLine(" [Summary]=@Summary,");
            sql.AppendLine(" [KeyWorks]=@KeyWorks,");
            sql.AppendLine(" [Content]=@Content");
            sql.AppendLine(" where [ID]=@ID");

            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(cmd, "@Title", DbType.String, obj.Title);
            db.AddInParameter(cmd, "@TypeID", DbType.Int32, obj.TypeID);
            db.AddInParameter(cmd, "@Status", DbType.Int32, obj.Status);
            db.AddInParameter(cmd, "@Author", DbType.String, obj.Author);
            db.AddInParameter(cmd, "@AddedDate", DbType.DateTime, obj.AddedDate);
            db.AddInParameter(cmd, "@Summary", DbType.String, obj.Summary);
            db.AddInParameter(cmd, "@KeyWorks", DbType.String, obj.KeyWorks);
            db.AddInParameter(cmd, "@Content", DbType.String, obj.Content);
            db.AddInParameter(cmd, "@ID", DbType.Int32, obj.ID);
            try
            {
                int count = db.ExecuteNonQuery(cmd);
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql.ToString());
            }
        }

        /// <summary>
        /// 获取记事模型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public MRecord GetModelById(int id)
        {
            string sql = "select * from TRecord where ID=@ID";
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@ID", DbType.Int32, id);
            try
            {
                using (IDataReader rdr = db.ExecuteReader(cmd))
                {
                    if (rdr.Read())
                    {
                        return new MRecord(rdr);
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql);
            }
        }

        /// <summary>
        /// 获取记事记录（分页）
        /// </summary>
        /// <param name="recordType"></param>
        /// <param name="status"></param>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="key"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetDataForPage(int recordType, int status, DateTime sdate, DateTime edate, string key, int pageIndex, int pageSize, out int recordCount)
        {
            StringBuilder strWhere = new StringBuilder();
            strWhere.AppendLine("where 1=1");
            if (recordType != 0)
            {
                strWhere.AppendLine(" and r.TypeID=@TypeID ");
            }
            if (status != RecordStatus.All)
            {
                strWhere.AppendLine(" and r.[Status]=@Status ");
            }
            else
            {
                if (recordType != 0)
                {
                    strWhere.AppendLine(" and r.[Status]<>" + RecordStatus.Deleted);
                }
            }
            if (sdate != DateTime.MinValue)
            {
                strWhere.AppendLine(" and datediff(d, AddDate, @StartDate) >= 0");
            }
            if (edate != DateTime.MinValue)
            {
                strWhere.AppendLine(" and datediff(d, AddDate, @EndDate) <= 0");
            }
            if (key != string.Empty)
            {
                strWhere.AppendLine(" and (Title like '%@Key%' or Content like '%@Key%')");
            }

            MyQueryParam pp = new MyQueryParam();
            pp.Keyfld = "r.ID";
            pp.Orderfld = "r.ID desc";
            pp.PageIndex = pageIndex;
            pp.PageSize = pageSize;
            pp.ReturnFields = "r.*, rt.Name, IsNull(tc.Num, 0) as Num";
            pp.ReturnFields += ", case when r.Status=" + RecordStatus.Ready + " then '<font color=blue>草稿</font>'";
            pp.ReturnFields += " when r.Status=" + RecordStatus.Published + " then '<font color=green>已发布</font>'";
            pp.ReturnFields += " when r.Status=" + RecordStatus.Deleted + " then '<font color=red>已删除</font>'";
            pp.ReturnFields += " else '<font color=blue>草稿</font>' end as StatusName";
            pp.TableJoin = "left join TRecordType rt on rt.ID=r.TypeID";
            pp.TableJoin += " left join (select FKID, count(0) as Num from TComment where [Type]=" + CommentType.RECORD + " group by FKID) as tc on tc.FKID=r.ID";
            pp.TableName = "TRecord r";
            pp.Where = strWhere.ToString();

            string sql = pp.GetSql();
            string sqlcount = pp.GetSqlCount();

            try
            {
                SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
                DbCommand sqlCountCmd = db.GetSqlStringCommand(sqlcount);
                db.AddInParameter(sqlCountCmd, "@TypeID", DbType.Int32, recordType);
                db.AddInParameter(sqlCountCmd, "@Status", DbType.Int32, status);
                if (sdate != DateTime.MinValue)
                    db.AddInParameter(sqlCountCmd, "@StartDate", DbType.DateTime, sdate);
                if (edate != DateTime.MinValue)
                    db.AddInParameter(sqlCountCmd, "@EndDate", DbType.DateTime, edate);
                db.AddInParameter(sqlCountCmd, "@Key", DbType.String, key);
                recordCount = (int)db.ExecuteScalar(sqlCountCmd);

                int pagecount = (int)Math.Ceiling((double)recordCount / (double)pageSize);
                if (pageIndex < 1)
                    pageIndex = 1;
                if (pageIndex > pagecount)
                    pageIndex = pagecount;
                pp.PageIndex = pageIndex;
                sql = pp.GetSql();
                DbCommand sqlCmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(sqlCmd, "@TypeID", DbType.Int32, recordType);
                db.AddInParameter(sqlCmd, "@Status", DbType.Int32, status);
                if (sdate != DateTime.MinValue)
                    db.AddInParameter(sqlCmd, "@StartDate", DbType.DateTime, sdate);
                if (edate != DateTime.MinValue)
                    db.AddInParameter(sqlCmd, "@EndDate", DbType.DateTime, edate);
                db.AddInParameter(sqlCmd, "@Key", DbType.String, key);
                return db.ExecuteDataSet(sqlCmd).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\r\n" + sql);
            }
        }

        /// <summary>
        /// 跟新记事状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int UpdateRecordType(int id, int status)
        {
            string sql = "update TRecord set [Status]=@Status where Id=@Id";
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Status", DbType.Int32, status);
            db.AddInParameter(cmd, "@Id", DbType.Int32, id);
            try
            {
                return db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception(sql, ex);
            }
        }

        public DataTable GetRecordTypes()
        {
            string sql = "select ID, Name, Remark from TRecordType";
            SqlDatabase db = new SqlDatabase(StaticVar.ConnectionString);
            try
            {
                DataTable dt = db.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(sql, ex);
            }
        }
    }
}
